﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassLibrary.Data;
using ClassLibrary.Entity;
using System.Data;

namespace ClassLibrary.DAO
{
    public class TblEmailDAO
    {
        private DBService _db = null;
        public TblEmailDAO(string ConnectionString)
        {
            _db = new DBService(ConnectionString);
        }

        public bool InsUpdItem(TblEmail email, int mode)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("sp_TblEmail_InsUp",
                    new string[] { "@IDEmail", "@EmailAddress", "@Status", "@Note", "@Price", "@Mode" },
                    new object[] { email.IDEmail, email.EmailAddress, email.Status, email.Note, email.Price, mode },
                    CommandType.StoredProcedure);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return flag;
        }

        public TblEmail GetEmailByID(int IDEmail)
        {
            TblEmail email = new TblEmail();
            try
            {
                DataTable dt = _db.ExeSP_DataTable("select * from TblEmail where IDEmail = @IDEMail",
                      new string[] { "@IDEmail" },
                    new object[] { IDEmail }
                    , CommandType.Text);
                foreach (DataRow dr in dt.Rows)
                {
                    email.IDEmail = IDEmail;
                    email.EmailAddress = dr["EmailAddress"].ToString();
                    email.Note = dr["Note"].ToString();
                    email.Price = Convert.ToDecimal(dr["Price"]);
                    email.Status = Convert.ToInt32(dr["Status"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return email;
        }



        public DataTable GetEmailByIDEmail(int IDEmail)
        {

            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("select * from TblEmail where IDEmail = @IDEMail",
                      new string[] { "@IDEmail" },
                    new object[] { IDEmail }
                    , CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }


        public bool DeleteEmail(int IDEmail)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("delete TblEmail where IDEmail = @IDEmail",
                    new string[] { "@IDEmail" },
                    new object[] { IDEmail },
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public bool UpdateNotes(int IDEmail, string Notes)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("update TblEmail set Note = @Note where IDEmail = @IDEmail",
                    new string[] { "@IDEmail", "@Note" },
                    new object[] { IDEmail, Notes },
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }


        public DataTable Get_AllEmail(string alphabet, int mode)
        {

            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("sp_GetAllEmail",
                    new string[] { "@alphabet", "@mode" },
                    new object[] { alphabet, mode },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable Get_AllEmailByCusID(string alphabet, int mode, int CusID)
        {

            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("sp_GetAllEmailByCusID",
                    new string[] { "@alphabet", "@mode", "@CusID" },
                    new object[] { alphabet, mode, CusID },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable UpStatus(int IDEmail)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("update TblEmail set Status = 1 where IDEmail = @IDEmail",
                    new string[] { "@IDEmail" },
                    new object[] { IDEmail },
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }


        public DataTable GetListEmail(string alp)
        {
            DataTable dt = null;
            try
            {
                dt = _db.ExeSP_DataTable("sp_ListEmail",
                    new string[] { "@alphabet"},
                    new object[] { alp },
                    CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public bool ConfirmEmail1(int IDEmail, int status)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("update TblEmail set [status] = @status where IDEmail = @IDEmail",
                    new string[] { "@IDEmail", "@status" },
                    new object[] { IDEmail, status },
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public bool DelEmail(int IDEmail)
        {
            bool flag = false;
            try
            {
                _db.ExeSP_NonQuery("delete TblEmail where IDEmail = @IDEmail",
                    new string[] { "@IDEmail" },
                    new object[] { IDEmail},
                    CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
